package com.neuedu.product.use.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.neuedu.product.use.bean.Tuser;
import com.neuedu.util.SqlUtil;

public class TUserController {

	public static void main(String[] args) {
		/*List<Tuser> listusers=selectAll();
        System.out.println(listusers);*/
		
		/*Tuser user=selectByUId(2);
		System.out.println(user);*/
		
		
		/*Tuser use=new Tuser();
		use.setUname("zero");
		use.setUpwd("adadfd");
		use.setUlevel(1);
		use.setUsex("男");
		use.setUaddress("合肥");
		
		int i=insert(use);
		System.out.println("影响的行数："+i);*/
		
		
		/*int i=delete(5);
		System.out.println("影响的行数："+i);*/
		
		/*Tuser use=selectByUId(6);
		use.setUpwd("123456");
		int i=update(use);
		System.out.println("影响的行数："+i);*/
		
		
		Tuser use=new Tuser();
		use.setUid(6);
		use.setUpwd("12345678");
	    use.setUname("tom1");
		int i=updateC(use);
	
	}
	//部分字段进行修改  uid不可少  
	public static int updateC(Tuser use) {
		int i=-1;
		
		Connection conn=null;
	     PreparedStatement pstm=null;
		 
		 try {
			conn=SqlUtil.getConn();
			//除了u_id，其他字段全部修改
			//拼接实现sql语句
			String sql="update t_use set ";
			/**
			 * 1:判断use中的每一个字段，当字段不为null是进行sql拼接
			 * 2:实际值，进行排序，是通过list
			 * */
			
			List<Object> listo=new ArrayList<>();
			
			if(use.getUname()!=null) {
				sql+="u_name=?,";
				listo.add(use.getUname());
			}
			
			if(use.getUpwd()!=null) {
				sql+="u_pwd=?,";
				listo.add(use.getUpwd());
			}
			
			if(use.getUsex()!=null) {
				sql+="u_sex=?,";
				listo.add(use.getUsex());
			}
			sql=sql.substring(0, sql.length()-1);//去除最后的“,”
			
			sql+=" where u_id=?";
			listo.add(use.getUid());
			
			/*System.out.println(sql);
			System.out.println(listo);*/
			
			pstm=conn.prepareStatement(sql);
			  //j+1:占位符的位置  ，listo.get(j)得到的下标为j的元素
		      for(int j=0;j<listo.size();j++) {
		    	  pstm.setObject(j+1, listo.get(j));
		      }
           
           i=pstm.executeUpdate();
		 } catch (SQLException e) {
			e.printStackTrace();
		}finally {
			/*SqlUtil.close(pstm,conn);*/
		}
		
		
		return i;
	}
	
	
	
	
	
	/**
	 *  修改
	 *  根据id进行其他字段修改
	 * */
	public static int update(Tuser use) {
		int i=-1;
		
		Connection conn=null;
	     PreparedStatement pstm=null;
		 
		 try {
			conn=SqlUtil.getConn();
			//除了u_id，其他字段全部修改
			String sql="update t_use set u_name=?,u_pwd=?,u_level=?,u_sex=?,u_address=? where u_id=?";
			pstm=conn.prepareStatement(sql);
			
			pstm.setObject(1, use.getUname());
			pstm.setObject(2, use.getUpwd());
           pstm.setObject(3, use.getUlevel());
           pstm.setObject(4, use.getUsex());
           pstm.setObject(5, use.getUaddress());
           pstm.setObject(6, use.getUid());
           
           i=pstm.executeUpdate();
		 } catch (SQLException e) {
			e.printStackTrace();
		}finally {
			SqlUtil.close(pstm,conn);
		}
		
		
		return i;
	}
	
	/**
	 * 删除
	 * 一条记录的删除
	 * */
	public static int delete(int uid) {
		int i=-1;
		
		Connection conn=null;
		PreparedStatement pstm=null;
		
		try {
			conn=SqlUtil.getConn();
			String sql="delete from t_use where u_id=?";
			pstm=conn.prepareStatement(sql);
			pstm.setObject(1, uid);
			i=pstm.executeUpdate();//删除、修改、添加
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			SqlUtil.close(pstm,conn);
		}
		
		
		
		
		
		return i;
	}
	
	
	/**
	 * 添加，
	 * 一条数据的添加
	 */
	 public static int insert(Tuser use) {
		 int i=-1;
          
		 Connection conn=null;
	     PreparedStatement pstm=null;
		 
		 try {
			conn=SqlUtil.getConn();
			String sql="insert into t_use(u_name,u_pwd,u_level,u_sex,u_address) values(?,?,?,?,?)";
			pstm=conn.prepareStatement(sql);
			pstm.setObject(1, use.getUname());
			pstm.setObject(2, use.getUpwd());
            pstm.setObject(3, use.getUlevel());
            pstm.setObject(4, use.getUsex());
            pstm.setObject(5, use.getUaddress());
            i=pstm.executeUpdate();
		 } catch (SQLException e) {
			e.printStackTrace();
		}finally {
			SqlUtil.close(pstm,conn);
		}
		 return i;
	 }
	
	
	
	/**
	    *  查找一条数据
	    *  根据id进行查找
	    * **/
	public static Tuser selectByUId(int uid) {
		Tuser use=new Tuser();
		//查找一条数据
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
	
		String url="jdbc:mysql://127.0.0.1:3306/productinfo";
		String user="root";
		String password="123456";
		
		try {
			conn=DriverManager.getConnection(url, user, password);
			//使用？作为占位符
			String sql="select * from t_use where u_id=?";
			pstm=conn.prepareStatement(sql);
			//使用pstm.setObject ,占位符的位置，添加数据
			//两个参数：第一参数：占位符的位置，第二个参数：值（实际值）
			pstm.setObject(1, uid);
			
			rs=pstm.executeQuery();//查找
			while(rs.next()) {
				
				use.setUid(rs.getInt("u_id"));
				use.setUname(rs.getString("u_name"));
				use.setUpwd(rs.getString("u_pwd"));
				use.setUlevel(rs.getInt("u_level"));
				use.setUsex(rs.getString("u_sex"));
				use.setUaddress(rs.getString("u_address"));
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null) {
					
				   rs.close();
					
				}
				
				if(pstm!=null) {
				   pstm.close();
				}
				
				if(conn!=null) {
				   conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		return use;
	}
	
	
   /**
    *  查找所有的数据
    * **/
	
	public static List<Tuser> selectAll(){
		//从数据库中提取数据放入list对象中
		List<Tuser> listusers=new ArrayList<>();
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
	
		String url="jdbc:mysql://127.0.0.1:3306/productinfo";
		String user="root";
		String password="123456";
		
		try {
			conn=DriverManager.getConnection(url, user, password);
			String sql="select * from t_use";
			pstm=conn.prepareStatement(sql);
			rs=pstm.executeQuery();
			while(rs.next()) {
				Tuser u=new Tuser();
				u.setUid(rs.getInt("u_id"));
				u.setUname(rs.getString("u_name"));
				u.setUpwd(rs.getString("u_pwd"));
				u.setUlevel(rs.getInt("u_level"));
				u.setUsex(rs.getString("u_sex"));
				u.setUaddress(rs.getString("u_address"));
				
				listusers.add(u);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null) {
					
				   rs.close();
					
				}
				
				if(pstm!=null) {
				   pstm.close();
				}
				
				if(conn!=null) {
				   conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		
		
		return listusers;
	}
	
	
	
}
